
import sys
import os
script_dir =os.path.dirname(os.path.abspath(__file__))
sys.path.append(script_dir)

sys.path.append(os.path.split(os.path.split(sys.path[0])[0])[0])
sys.path.append(r"D:\my_work\ac_lingxing_api\ac_lingxing_api")
import pandas as pd
import time
import os
from database.mysql_utils import MysqlUtils

from  openpyxl import  Workbook
from sqlalchemy import create_engine
import datetime

class tools:
    Entity = 1
    Operation = 2
    CampaignId = 3
    AdGroupId = 4
    PortfolioId = 5
    AdId = 6
    KeywordId = 7
    ProductTargetingId = 8
    TargetingType = 13
    State = 14
    DailyBudget = 15
    SKU = 16
    ASIN = 17

    @staticmethod
    def get_new_lst():
        lst = ['Sponsored Products'] + [''] * 25
        return lst 

class work_sheet:
    def __init__(self, sid, browserName, seller_id, browserOauth, country):
        self.sheet_name = "Sponsored Products Campaigns"
        self.title = ['Product', 'Entity', 'Operation', 'Campaign Id', 'Ad Group Id', 
           'Portfolio Id', 'Ad Id', 'Keyword Id', 'Product Targeting Id', 'Campaign Name', 
           'Ad Group Name', 'Start Date', 'End Date', 'Targeting Type', 'State', 'Daily Budget', 
           'SKU', 'ASIN', 'Ad Group Default Bid', 'Bid', 'Keyword Text', 'Match Type', 'Bidding Strategy', 
           'Placement', 'Percentage', 'Product Targeting Expression']
        self.file_path = r"C:/Users/Administrator/Desktop/Super Browser/" + browserName + "/ad" + datetime.datetime.now().strftime("%Y-%m-%d") +"/"
        self.file_name = f"bulk_{sid}_"  + str(int(time.time())) + ".xlsx"
        self.save_path = os.path.join(self.file_path, self.file_name)
        self.wb = Workbook()
        self.ws =  self.wb.create_sheet(self.sheet_name, 0)
        self.ws.append(self.title)
        self.wb.remove(self.wb["Sheet"])
        self.info = {
            "sid": sid, 
            "account_name": browserName,
            "seller_id": seller_id,
            "save_path":self.save_path,
            "browserOauth":browserOauth,
            "country":country
        }

    
    def add_data(self, data_lst):
        self.ws.append(data_lst)

    def save_file(self):
        if self.ws.max_row > 1:
            if not os.path.exists(self.file_path):
                os.makedirs(self.file_path)
            
            self.wb.save(self.save_path)


class add_sku:

    def __init__(self):
        self.engine_base = MysqlUtils("ac_base_data")
        self.engine_rpa = MysqlUtils("rpa")
        self.engine_ad_api = MysqlUtils("ad_api")
        self.date_today = datetime.datetime.now().strftime("%Y-%m-%d")
        self.date_today2 = (datetime.datetime.now()).strftime("%y%m%d")
    
    def get_all_sid(self):
        sql  = f"select lx_sid, lx_seller_id, lx_country, store_name, zn_id from store_info where lx_country in ('美国', '英国', '意大利', '德国') and lx_status = 1"
        account_data = pd.read_sql(sql, con=self.engine_base.engine)
        return account_data
    def get_all_asin(self, sid_data):
        sql = f'select asin, seller_sku, sid from lingxing_listing where sid in ({sid_data}) and is_delete <> 1  and (quantity + afn_fulfillable_quantity + reserved_fc_transfers + reserved_fc_processing + reserved_customerorders + afn_inbound_shipped_quantity + afn_inbound_receiving_quantity) > 0'
        asin_data = pd.read_sql(sql, con=self.engine_base.engine)
        print(asin_data)
        return asin_data
    def get_asin_ad_data(self, ad_str):
        # 这里不直接根据状态筛选是因为，这里的状态是商品的状态，不是广告活动的状态
        sql  = f"select * from sp_product_ads_data_api where   DATE(update_time) = (select max(DATE(update_time)) from sp_product_ads_data_api) and ad_group_id in ({ad_str}) "
        asin_ad_data = pd.read_sql(sql, con=self.engine_ad_api.engine)
        return asin_ad_data 
    
    def get_campaigns_data(self):
        # 只筛选广告活动为开启的广告活动
        sql = f"select campaign_id, profile_id,state, targeting_type, daily_budget, portfolio_id, sid, account from sp_campaigns_data_api where   DATE(update_time) = (select max(DATE(update_time)) from sp_campaigns_data_api) and state = 'enabled'"
        campaigns_data = pd.read_sql(sql, con=self.engine_ad_api.engine)
        return campaigns_data
    def get_adgroup_data(self):
        # 只筛选广告活动为开启的广告活动
        sql = f"select ad_group_id, campaign_id from sp_ad_groups_data_api where   DATE(update_time) = (select max(DATE(update_time)) from sp_campaigns_data_api) and state = 'enabled'"
        ad_group_data = pd.read_sql(sql, con=self.engine_ad_api.engine)
        return ad_group_data
    
    def get_all_ad(self):
        
        sid_data = ",".join([str(i) for i in list(self.account_data['lx_sid'])])
        asin_data = self.get_all_asin(sid_data)
        asin = ",".join(["'"+str(i)+"'" for i in list(set(asin_data['asin']))])

        
        # asin_ad_data = asin_ad_data[asin_ad_data['state'] == "enabled"]
        campaigns_data = self.get_campaigns_data()
        ad_group_data = self.get_adgroup_data()
        
        #只保留  广告活动、广告组为开启状态的数据
        ad_ = ad_group_data[ad_group_data['campaign_id'].isin(list(set(campaigns_data['campaign_id'])))]['ad_group_id']
        ad_str = ",".join(["'" + str(ad) + "'" for ad in list(set(ad_))])
        
        asin_ad_data = self.get_asin_ad_data(ad_str)
        
        
        # asin_ad_data = asin_ad_data[asin_ad_data['campaign_id'].isin(list(set(campaigns_data['campaign_id'])))]
        # asin_ad_data = asin_ad_data[asin_ad_data['ad_group_id'].isin(list(set(ad_group_data['ad_group_id'])))]

        asin_ad_data = asin_ad_data.merge(campaigns_data, how='left', on=["profile_id", "campaign_id", "sid", "account"])
        return asin_data, asin_ad_data
    

    def make_excel(self, data):
        sid_lst = list(set(data['lx_sid']))
        # print("account_lst", account_lst)
        excel_account = {}
        for sid in sid_lst:
            browserName = list(data.loc[data['lx_sid'] == sid, 'store_name'])[0]
            seller_id = list(data.loc[data['lx_sid'] == sid, 'lx_seller_id'])[0]
            browserOauth = list(data.loc[data['lx_sid'] == sid, 'zn_id'])[0]
            country = list(data.loc[data['lx_sid'] == sid, 'lx_country'])[0]
            excel_account[sid] = work_sheet(sid, browserName, seller_id, browserOauth, country)
        return excel_account
    

    def campaign_handler(self, ad_group_sid_dict):
        
        
        lst_campaign = tools.get_new_lst()
        lst_campaign[tools.Entity] = 'campaign'
        lst_campaign[tools.CampaignId] = ad_group_sid_dict['campaign_id']
        if ad_group_sid_dict['portfolio_id'] == 0:
            pass
        else:
            lst_campaign[tools.PortfolioId] = ad_group_sid_dict['portfolio_id']
        lst_campaign[tools.State] = "enabled"
        lst_campaign[tools.TargetingType] = ad_group_sid_dict['targeting_type']
        lst_campaign[tools.DailyBudget] = ad_group_sid_dict['daily_budget']
        sid = ad_group_sid_dict['sid']
        self.excel_account[sid].add_data(lst_campaign)

    def ad_group_handler(self, ad_group_sid_dict, null_sku):
        lst_ad_group = tools.get_new_lst()
        lst_ad_group[tools.Entity] = 'ad group'
        lst_ad_group[tools.CampaignId] = ad_group_sid_dict['campaign_id']
        lst_ad_group[tools.AdGroupId] = ad_group_sid_dict['ad_group_id']

        lst_ad_group[tools.State] = "enabled"
        sid = ad_group_sid_dict['sid']
        self.excel_account[sid].add_data(lst_ad_group)
        for sku in null_sku:
            lst_pro_ad = tools.get_new_lst()
            lst_pro_ad[tools.Entity] = 'product ad'
            lst_pro_ad[tools.Operation] = 'create'
            lst_pro_ad[tools.CampaignId] = ad_group_sid_dict['campaign_id']
            lst_pro_ad[tools.AdGroupId] = ad_group_sid_dict['ad_group_id']
            lst_pro_ad[tools.State] = 'enabled'
            lst_pro_ad[tools.ASIN] = ad_group_sid_dict['asin']
            lst_pro_ad[tools.SKU] = sku
            self.excel_account[sid].add_data(lst_pro_ad)

        

    def ad_handler(self, ad_group_sid, null_sku):
        ad_group_sid_dict = ad_group_sid.iloc[0].to_dict()
        self.campaign_handler(ad_group_sid_dict)
        self.ad_group_handler(ad_group_sid_dict, null_sku)


    def main(self):
        self.account_data = self.get_all_sid().dropna(how="any")
        self.excel_account = self.make_excel(self.account_data)
        # asin_data:所有asin的数据
        # asin_ad_data ： 所有广告的数据
        asin_data, asin_ad_data = self.get_all_ad()
        asin_ad_data['portfolio_id'] = asin_ad_data['portfolio_id'].fillna(0).astype("int")

        # data_：所有的商品广告里面的数据
        for asin in set(asin_ad_data['asin']):
            # 获取改asin的所有广告数据
            asin_ad_data_oneasin = asin_ad_data[asin_ad_data['asin'] == asin]

            # 获取该asin下的店铺、sku
            asin_data_oneasin = asin_data[asin_data["asin"]==asin]
            # 查看该asin下的所有广告组
            for ad_group in set(asin_ad_data_oneasin['ad_group_id']):
                # 获取该广告组下的产品信息
                ad_group = asin_ad_data_oneasin[asin_ad_data_oneasin['ad_group_id'] == ad_group]
                for sid in set(ad_group['sid']):
                    ad_group_sid = ad_group[ad_group['sid'] == sid]
                    asin_data_oneasin_sid = asin_data_oneasin[asin_data_oneasin['sid'] == sid]
                    

                    # 获取广告组下的sku
                    ad_group_sid_sku = set(ad_group_sid['sku'])
                    # 店铺里面的sku
                    asin_data_oneasin_sid_sku = set(asin_data_oneasin_sid['seller_sku'])
                    # 获取没有添加到广告组里面的sku
                    null_sku = asin_data_oneasin_sid_sku - ad_group_sid_sku
                    if len(ad_group_sid)>0 and len(null_sku) > 0:
                        self.ad_handler(ad_group_sid, null_sku)

                    # ad_info = ad_group_sid[ad_group_sid['sku'].isin(null_sku)]
        lst = []
        for work_excel in self.excel_account.values():
            if (work_excel.ws.max_row) > 1:
                lst.append(work_excel.info)
                work_excel.save_file()
        save_df = pd.DataFrame(lst)
        save_df['update_time'] = datetime.datetime.now()
        save_df['is_upload'] = 0
        save_df.to_sql("new_sku_insert", con=self.engine_rpa.engine, index=False, if_exists="append")






if __name__ == "__main__":

    add_s = add_sku()
    add_s.main()